package com.hsnn.medstgmini.base.dict.dao.impl;

import java.io.OutputStream;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.DicDrugmaterialDao;
import com.hsnn.medstgmini.base.dict.model.DicDrugmaterial;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseDate;

/**
 * 基础库 数据字典 材质字典 接口 实现类
 * @author hss@njhsnn
 * 2015年6月9日
 */
@Repository
public class DicDrugmaterialDaoImpl extends BaseHibernateDAO implements DicDrugmaterialDao {
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	/**
	 * 基础库 数据字典 材质字典 保存（增加和修改）
	 * @param dicDrugmaterial
	 */
	@Override
	public void addAndEdit(Pagination page) {
		/*getSession().saveOrUpdate(dicDrugmaterial);
		getSession().flush();
		writeLog(dicDrugmaterial.getMaterialId());*/
		Session session = sessionFactory.getCurrentSession();
		DicDrugmaterial dicDrugmaterial = (DicDrugmaterial) page.getForm();
		if(StringUtils.isNotBlank(dicDrugmaterial.getMaterialId())){
			StringBuffer sql = new StringBuffer();
			sql.append("update dic_drugmaterial set  material_code = :materialCode, material_name = :materialName,upd_datetime = :updDatetime, upd_user = :updUser where material_id = :materialId");
			Query query = session.createSQLQuery(sql.toString());
			query.setString("materialCode", dicDrugmaterial.getMaterialCode());
			query.setString("materialName", dicDrugmaterial.getMaterialName());
			query.setString("updUser", dicDrugmaterial.getUpdUser());
			query.setString("updDatetime", ParseDate.parseFullFormat(new Date()));
			query.setString("materialId", dicDrugmaterial.getMaterialId());
			query.executeUpdate();
			session.flush();
			writeLog(dicDrugmaterial.getMaterialId());
		}else{
			String uuid = UUID.randomUUID().toString();
			StringBuffer sql = new StringBuffer();
			sql.append(" insert into dic_drugmaterial (material_id,material_code,material_name,status,upd_datetime,upd_user) values ")
			   .append("  (:uuid,:materialCode,:materialName, ")
			   .append(" 1, :updDatetime,:updUser ) ");
			Query query = session.createSQLQuery(sql.toString());
			query.setString("uuid", uuid);
			query.setString("materialCode", dicDrugmaterial.getMaterialCode());
			query.setString("materialName", dicDrugmaterial.getMaterialName());
			query.setString("updUser", dicDrugmaterial.getUpdUser());
			query.setString("updDatetime", ParseDate.parseFullFormat(new Date()));
			query.executeUpdate();
			session.flush();
			writeLog(uuid);
		}
	}

	/**
	 * 基础库 数据字典 材质字典 查询
	 * @param dicDrugmaterial
	 */
	@Override
	public Pagination queryDicDrugmaterialList(
			Pagination page) {
		String materialCode = page.getConditions().get("materialCode");
		String materialName = page.getConditions().get("materialName");
		String status = page.getConditions().get("status");
		
		StringBuffer hql = new StringBuffer();
		hql.append(" select a.material_id as materialId, a.material_code as materialCode, a.material_name as materialName, a.status as status from dic_drugmaterial  a where 1=1 ");
		if(StringUtils.isNotBlank(materialCode)){
			hql.append(" and a.material_code = :materialCode ");
		} 
		if(StringUtils.isNotBlank(materialName)){
			hql.append(" and a.material_name like :materialName ");
		}
		if(StringUtils.isNotBlank(status)){
			hql.append(" and a.status = :status ");
		}
		Query query = getSession().createSQLQuery(hql.toString());
		StringBuffer hqlCount = new StringBuffer();
		hqlCount.append(" select count(*) from (").append(hql).append(")t");
		Query queryCount = getSession().createSQLQuery(hqlCount.toString());
		if(StringUtils.isNotBlank(materialCode)){
			query.setString("materialCode", materialCode);
			queryCount.setString("materialCode", materialCode);
		}
		if(StringUtils.isNotBlank(materialName)){
			query.setString("materialName", "%"+materialName+"%");
			queryCount.setString("materialName", "%"+materialName+"%");
		}
		if(StringUtils.isNotBlank(status)){
			query.setString("status", status);
			queryCount.setString("status", status);
		}
		page.setRecords(Long.parseLong(queryCount.uniqueResult().toString()));
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);//转换成map
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		page.setRows(query.list());
		return page;
	}
	
	

	/**
	 * 基础库 数据字典 材质字典 根据id查询数据
	 * @param Id
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<DicDrugmaterial> queryDicDrugmaterialById(String Id) {
		StringBuffer hql = new StringBuffer();
		hql.append(" from DicDrugmaterial where material_id = ?");
		Query query = getSession().createQuery(hql.toString());
		query.setParameter(0, Id);
		return query.list();
	}
	
	/**
	 * 根据code或name查看有无重复数据
	 * @param code,name
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<DicDrugmaterial> getMaterialBy(String key,String value) {
		StringBuffer hql = new StringBuffer();
		hql.append(" from DicDrugmaterial where ")
		   .append(key)
		   .append(" = ?");
		Query query = getSession().createQuery(hql.toString());
		query.setParameter(0, value);
		return query.list();
	}
	/**
	 * 
	 *@category 启用和停用
	 *@author wangbing
	 *@date 2015年6月9日
	 *Pagination
	 */
	@Override
	public int changeStatus(DicDrugmaterial dicDrugmaterial) {
		Session session = getSession();
		StringBuffer sql = new StringBuffer();
		sql.append("update dic_drugmaterial  set ")
		  .append(" status=:status,upd_datetime=:updDatetime,upd_user=:updUser ")
		  .append(" where material_id= :materialId and status <> :status ");
		Query update = session.createSQLQuery(sql.toString());
		update.setLong("status",dicDrugmaterial.getStatus());
		update.setTimestamp("updDatetime", new Date());
		update.setString("updUser", dicDrugmaterial.getUpdUser());
		update.setString("materialId", dicDrugmaterial.getMaterialId());
		int count = update.executeUpdate();
		session.flush();
		writeLog(dicDrugmaterial.getMaterialId());
		return count;
	}
	
	/**
	 * 
	 *@category 写日志
	 *@author wangbing
	 *@date 2015年6月9日
	 *Pagination
	 */
	@SuppressWarnings("unchecked")
	@Override
	public void writeLog(String materialId){
		Session session = getSession();
		StringBuffer detail = new StringBuffer();
		StringBuffer sb = new StringBuffer()
		.append("select material_code materialCode,material_name materialName,status from dic_drugmaterial where material_id = :materialId ")
		.append(" union all ")
		.append(" select t.* from (select material_code materialCode,material_name materialName,status from dic_drugmaterial_log where material_id = :materialId order by upd_datetime desc limit 1) t");
		Query query1 = session.createSQLQuery(sb.toString());
		query1.setString("materialId", materialId);
		query1.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> list = query1.list();
		if(list.size()==1){
			//字段名:修改前数据,修改后数据
			detail.append(";编码:").append(list.get(0).get("materialCode").toString())
			      .append(";名称:").append(list.get(0).get("materialName").toString())
			      .append(";状态:").append("1".equals(list.get(0).get("status").toString())?"启用":"停用");
		}else{
			if(!list.get(0).get("materialCode").toString().equals(list.get(1).get("materialCode").toString())){
				detail.append(";编码:").append(list.get(1).get("materialCode").toString()).append(",").append(list.get(0).get("materialCode").toString());
			}
			if(!list.get(0).get("materialName").toString().equals(list.get(1).get("materialName").toString())){
				detail.append(";名称:").append(list.get(1).get("materialName").toString()).append(",").append(list.get(0).get("materialName").toString());
			}
			if(!list.get(0).get("status").toString().equals(list.get(1).get("status").toString())){
				detail.append(";状态:").append("1".equals(list.get(1).get("status").toString())?"启用":"停用").append(",").append("1".equals(list.get(0).get("status").toString())?"启用":"停用");
			}
		}
		detail.delete(0, 1);
		
		StringBuffer sql = new StringBuffer();
		sql.append("insert into dic_drugmaterial_log(log_id,material_id,material_code,material_name,  ")
		   .append("status,upd_datetime,upd_user,sort_id,detail) select ")
		   .append(" UUID(),t.*,:detail from dic_drugmaterial t where t.material_id= :materialId ");
		Query query=session.createSQLQuery(sql.toString());
		query.setString("materialId", materialId);
		query.setString("detail", detail.toString());
		query.executeUpdate();
		session.flush();
		
	}
	
	@Override
	public void exportExcel(Pagination page,OutputStream os){
        HSSFWorkbook wb = new HSSFWorkbook();  
        HSSFSheet sheet = wb.createSheet("材质");  
        HSSFRow row = sheet.createRow((int) 0);  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        
        HSSFCell cell = null;
        String[] headers = {"材质编码","材质名称","状态"};
        for(int i = 0;i<headers.length;i++){
        	 cell = row.createCell(i);
             cell.setCellValue(headers[i]);  
             cell.setCellStyle(style);  
        }
        
        List<Object> list = getMaterialNoPage(page);
        for (int i = 0; i < list.size(); i++)  
        {  
            row = sheet.createRow((int) i + 1);  
            Object[] obj = (Object[]) list.get(i);  
            for(int j = 0;j<obj.length;j++){
            	row.createCell(j).setCellValue(obj[j]==null?"":obj[j].toString());  
            }
            
        }  
        try {   
            //OutputStream os = new FileOutputStream("D:/drugMaterial.xls");  
            wb.write(os);  
            os.close();  
            page.setSuccess(true);
        } catch (Exception e){  
           page.setSuccess(false);
            e.printStackTrace();  
        } 
       
	}
	
	@SuppressWarnings("unchecked")
	private List<Object> getMaterialNoPage(Pagination page){
		String materialCode = page.getConditions().get("materialCode");
		String materialName = page.getConditions().get("materialName");
		String status = page.getConditions().get("status");
		StringBuffer sql = new StringBuffer();
		sql.append(" select a.material_code, a.material_name, case a.status when '0' then '停用' else '启用' end status from dic_drugmaterial  a where 1=1 ");
		if(StringUtils.isNotBlank(materialCode)){
			sql.append(" and a.material_code = :materialCode ");
		} 
		if(StringUtils.isNotBlank(materialName)){
			sql.append(" and a.material_name like :materialName ");
		}
		if(StringUtils.isNotBlank(status)){
			sql.append(" and a.status = :status ");
		}
		Query query = getSession().createSQLQuery(sql.toString());
		if (StringUtils.isNotBlank(materialCode)) {
			query.setParameter("materialCode",  materialCode );
		}
		if (StringUtils.isNotBlank(materialName)) {
			query.setParameter("materialName", "%" + materialName + "%");
		}
		if (StringUtils.isNotBlank(status)) {
			query.setParameter("status", "0".equals(status)?"停用":"启用");
		}
		return query.list();
	}
}
